package com.tran.app.dao

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Update
import com.tran.app.data.CourseSelection
import com.tran.app.data.UserInfo


@Dao
interface CourseSelectionDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(courseSelection: CourseSelection)

    @Update
    fun update(courseSelection: CourseSelection)

    @Delete
    fun delete(courseSelection: CourseSelection)

    @Query("SELECT * FROM CourseSelection")
    fun getAllCourseSelections(): List<CourseSelection>

    // 获取某个学生所选的所有课程
    @Query("SELECT * FROM CourseSelection WHERE studentId = :studentId")
    fun getCourseSelectionsByStudentId(studentId: Int): List<CourseSelection>

    @Query("SELECT * FROM CourseSelection WHERE studentId = :studentId  AND courseId = :courseId")
    fun getCourseSelectionByStudentCourseId(studentId: Int, courseId: Int): CourseSelection

    @Query(
        """  
        SELECT u.* FROM UserInfo u  
        WHERE u.id IN (  
            SELECT studentId FROM CourseSelection WHERE courseId = :courseId  
        )  
    """
    )
    fun getStudentsByCourseId(courseId: Int): List<UserInfo>

    @Query("DELETE FROM CourseSelection WHERE id = :courseSelectionId")
    fun deleteByCourseSelectionId(courseSelectionId: Long)
}